 /****** Object:  Stored Procedure CustomerProductsGetList    Script Date: Sunday, February 11, 2007 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spCustomerProductsGetListByCustomer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spCustomerProductsGetListByCustomer]
GO

	
CREATE PROCEDURE spCustomerProductsGetListByCustomer
	@UserID int,
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempCustomerProducts (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	UserID int,	
	ProductCode varchar(10)	
)

DECLARE @sql nvarchar(2000)

-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempCustomerProducts ([UserID],[ProductCode]) SELECT '
IF (@PageSize > 0)
	SET @sql = @sql + ' TOP ' + CAST(@Page*@PageSize as nvarchar) 

SET @sql = @sql + ' [UserID],[CustomerProducts].[ProductCode] FROM [CustomerProducts] 
				LEFT JOIN BulkOrders ON BulkOrders.ProductCode = [CustomerProducts].[ProductCode]
				LEFT JOIN Suppliers ON Suppliers.SupplierCode = BulkOrders.SupplierCode
				LEFT JOIN ConPoints ON ConPoints.ConpointID = Suppliers.ConPointID
		 WHERE '
		+ CAST(@UserID as nvarchar) + '=-1 OR ' + CAST(@UserID as nvarchar) + '=UserID ORDER BY [' + @OrderBy + N'] ' + @OrderDirection

EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [CustomerProducts]
WHERE (@UserID=-1 OR UserID=@UserID)

SELECT
	[CustomerProducts].[UserID],
	[CustomerProducts].[ProductCode],
	[ReservedQty],
	[ReservedDate],
	[NumberOfDay],
	[USER].LOGIN_NAME as LoginName,
	ConPoints.Description as ConPointDescription
FROM
	#TempCustomerProducts AS tblTemp JOIN [CustomerProducts] ON
	tblTemp.UserID = [CustomerProducts].UserID  AND 	
	tblTemp.ProductCode = [CustomerProducts].ProductCode 		
	LEFT JOIN [USER] ON tblTemp.UserID = [USER].US_ID_PK
	LEFT JOIN BulkOrders ON BulkOrders.ProductCode = [CustomerProducts].[ProductCode]
				LEFT JOIN Suppliers ON Suppliers.SupplierCode = BulkOrders.SupplierCode
				LEFT JOIN ConPoints ON ConPoints.ConpointID = Suppliers.ConPointID
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempCustomerProducts

GO

	
